﻿Imports System.Data.SqlClient

Public Class DataProvider
    '.\SQLEXPRESS
    Public Shared cnStr As String = "Server=.\SQLEXPRESS; Database=QL_Bida; integrated Security=SSPI;"

    Public Shared Function LoadSQL(ByVal sql As String) As DataTable

        Dim cn As New SqlConnection(cnStr)
        Dim da As New SqlDataAdapter(sql, cnStr)

        Dim dt As New DataTable()

        da.Fill(dt)

        Return dt
    End Function

    Public Shared Sub ExcuteQuery(ByVal sql As String)
        Dim cn As New SqlConnection(cnStr)
        Dim cmd As New SqlCommand(sql, cn)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

    Public Shared Function LoadSqlProc(ByVal sql As String, ByRef listpara As List(Of SqlParameter)) As DataTable
        Dim cn As New SqlConnection(cnStr)
        Dim da As New SqlDataAdapter(sql, cn)
        da.SelectCommand.CommandType = CommandType.StoredProcedure

        If (listpara.Count <> 0) Then
            For Each temp As SqlParameter In listpara
                da.SelectCommand.Parameters.Add(temp)
            Next
        End If
        Dim dt As New DataTable()
        da.Fill(dt)
    End Function

    Public Shared Sub ExcuteQueryProc(ByVal sql As String, ByRef listpara As List(Of SqlParameter))
        Dim cn As New SqlConnection(cnStr)
        Dim cmd As New SqlCommand(sql, cn)

        cmd.CommandType = CommandType.StoredProcedure
        For Each temp As SqlParameter In listpara
            cmd.Parameters.Add(temp)
        Next

        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()


    End Sub

    Public Shared Function LoadSqlProcH(ByVal sql As String, ByRef listpara As List(Of SqlParameter)) As DataTable
        Dim cn As New SqlConnection(cnStr)
        Dim da As New SqlDataAdapter(sql, cn)
        da.SelectCommand.CommandType = CommandType.StoredProcedure

        If (listpara.Count <> 0) Then
            For Each temp As SqlParameter In listpara
                da.SelectCommand.Parameters.Add(temp)
            Next
        End If
        Dim dt As New DataTable()
        da.Fill(dt)
        Return dt
    End Function


End Class
